﻿Public Class perVisitas
    Inherits perPadrao
#Region "Variáveis"

    Private objBuscaId As New perProximoId

#End Region

#Region "Métodos"

    Public Function AlterarHistoricoVisita(ByVal iIDHistorico As Int32, ByVal iIDCliente As Int32, ByVal iIDContato As Int32, _
                                           ByVal dtDiaVisita As Date, ByVal sObservacoes As String) As Boolean

        Dim sSql As String

        Try
            sSql = "UPDATE DC_HistVisitass SET IDCliente = @IDCliente, " & vbCrLf
            sSql &= "                         IDContato = @IDContato, " & vbCrLf
            sSql &= "                         Data = '@Data'," & vbCrLf
            sSql &= "                         Observacoes = '@Observacoes'" & vbCrLf
            sSql &= " WHERE IDHistorico = @IDHistorico"

            With MyBase.SQLCmd.Parameters
                .Clear()
                .AddWithValue("@IDHistorico", iIDHistorico)
                .AddWithValue("@IDCliente", iIDCliente)
                .AddWithValue("@IDContato", iIDContato)
                .AddWithValue("@Data", dtDiaVisita.Date)
                .AddWithValue("@Observacoes", sObservacoes)
            End With

            MyBase.executarAcao(sSql)
            Return True

        Catch ex As Exception
            Return False
            Throw New Exception("Ocorreu uma exceção no método 'AlterarHistoricoVisita'." & Environment.NewLine & Environment.NewLine & ex.Message)
        End Try

    End Function
    Public Function AlterarVisitaAgendada(ByVal iIDCliente As Int32, ByVal iIDContato As Int32, ByVal dtDiaVisita As Date, _
                                          ByVal sHoraVisita As String, ByVal sObservacoes As String) As Boolean

        Dim sSql As String

        Try
            sSql = "UPDATE DC_CadVisitas SET HoraVisita = @HoraVisita,"
            sSql &= "                         Observacoes = @Observacoes"
            sSql &= " WHERE IDCliente = @IDCliente"
            sSql &= "   AND IDContato = @IDContato"
            sSql &= "   AND DataVisita = @Data"

            With MyBase.SQLCmd.Parameters
                .Clear()
                .AddWithValue("@IDCliente", iIDCliente)
                .AddWithValue("@IDContato", iIDContato)
                .AddWithValue("@Data", dtDiaVisita.Date)
                .AddWithValue("@HoraVisita", sHoraVisita)
                .AddWithValue("@Observacoes", sObservacoes)
            End With

            MyBase.executarAcao(sSql)
            Return True
        Catch ex As Exception
            Throw New Exception("Ocorreu uma exceção no método 'InserirVisitaAgendada'" & Environment.NewLine & Environment.NewLine & ex.Message)
            Return False
        End Try

    End Function
    Public Function ExcluirVisitaAgendada(ByVal iIDCliente As Int32, ByVal iIDContato As Int32, ByVal dtDiaVisita As Date) As Boolean

        Dim sSql As String

        Try
            sSql = "DELETE DC_CadVisitas"
            sSql &= " WHERE IDCliente = @IDCliente"
            sSql &= "   AND IDContato = @IDContato"
            sSql &= "   AND DataVisita = @Data"

            With MyBase.SQLCmd.Parameters
                .Clear()
                .AddWithValue("@IDCliente", iIDCliente)
                .AddWithValue("@IDContato", iIDContato)
                .AddWithValue("@Data", dtDiaVisita.Date)
            End With

            MyBase.executarAcao(sSql)
            Return True
        Catch ex As Exception
            Throw New Exception("Ocorreu uma exceção no método 'InserirVisitaAgendada'" & Environment.NewLine & Environment.NewLine & ex.Message)
            Return False
        End Try

    End Function
    Public Function InserirHistoricoVisita(ByVal iIDCliente As Int32, ByVal iIDContato As Int32, _
                                           ByVal dtData As Date, ByVal sObservacoes As String) As Int32

        Dim sSql As String
        Dim iIDHistorico As Int32

        Try
            iIDHistorico = Me.objBuscaId.BuscaID("IDHistorico", "DC_HistVisitass")

            sSql = "INSERT INTO DC_HistVisitass" & vbCrLf
            sSql &= "     VALUES( " & iIDHistorico & "," & vbCrLf
            sSql &= "            " & iIDCliente & ", " & vbCrLf
            sSql &= "            " & iIDContato & ", " & vbCrLf
            sSql &= "            '" & dtData.Date & "', " & vbCrLf
            sSql &= "            '" & sObservacoes & "')" & vbCrLf

            MyBase.executarAcao(sSql)

        Catch ex As Exception

            iIDHistorico = 0
            Throw New Exception("Exceção no método 'InserirHistoricoVisita'." & Environment.NewLine & Environment.NewLine & ex.Message)

        End Try

        Return iIDHistorico

    End Function
    Public Function InserirVisitaAgendada(ByVal iIDCliente As Int32, ByVal iIDContato As Int32, ByVal dtDiaVisita As Date, _
                                            ByVal sHoraVisita As Date, ByVal sObservacoes As String, ByVal iIDUsuario As Int32) As Boolean

        Dim sSql As String

        Try
            sSql = "INSERT INTO DC_CadVisitas("
            sSql &= "IDCliente," & vbCrLf
            sSql &= "IDContato," & vbCrLf
            sSql &= "DataVisita," & vbCrLf
            sSql &= "HoraVisita," & vbCrLf
            sSql &= "Observacoes," & vbCrLf
            sSql &= "IDUsuario," & vbCrLf
            sSql &= "DataCadastro" & vbCrLf
            sSql &= ")" & vbCrLf
            sSql &= "VALUES("
            sSql &= iIDCliente & "," & vbCrLf
            sSql &= iIDContato & "," & vbCrLf
            sSql &= "'" & dtDiaVisita & "'," & vbCrLf
            sSql &= "'" & sHoraVisita & "', " & vbCrLf
            sSql &= "'" & sObservacoes & "'," & vbCrLf
            sSql &= iIDUsuario & "," & vbCrLf
            sSql &= "'" & Date.Now.Date & "'"
            sSql &= ")"

            MyBase.executarAcao(sSql)
            Return True
        Catch ex As Exception
            Throw New Exception("Ocorreu uma exceção no método 'InserirVisitaAgendada'" & Environment.NewLine & Environment.NewLine & ex.Message)
            Return False
        End Try

    End Function
    Public Function MarcarVisitaRealizada(ByVal IDCliente As Int32, ByVal iIDContato As Int32, ByVal dtData As Date, _
                                          ByVal IDHistorico As Int32) As Boolean
        Dim sSql As String

        Try
            sSql = "UPDATE DC_CadVisitas" & vbCrLf
            sSql &= "  SET IdHistorico = " & IDHistorico & vbCrLf
            sSql &= "WHERE IDCliente = " & IDCliente & vbCrLf
            sSql &= "  AND IDContato = " & iIDContato & vbCrLf
            sSql &= "  AND DataVisita ='" & dtData.Date & "'"

            MyBase.executarAcao(sSql)
            Return True

        Catch ex As Exception
            Return False
            Throw New Exception("Exceção no método MarcarVisitaRealizada. " & Environment.NewLine & Environment.NewLine & ex.Message)

        End Try

    End Function
    Public Function NVisitasPorDia(ByVal dtDataBase As Date) As Int32
        Dim sSql As String
        Dim dtResultado As New DataSet

        Try
            sSql = "SELECT COUNT(DataVisita) as NVisitas" & vbCrLf
            sSql &= "  FROM DC_CadVisitas" & vbCrLf
            sSql &= " WHERE DC_CadVisitas.DataVisita = @Data"

            With MyBase.SQLCmd.Parameters
                .Clear()
                .AddWithValue("@Data", dtDataBase.Date)
            End With

            dtResultado = MyBase.executarConsulta(sSql, "DC_CadVisitas")

            If (dtResultado.Tables("DC_CadVisitas").Rows.Count > 0) Then
                Return dtResultado.Tables("DC_CadVisitas").Rows(0).Item("NVisitas")
            Else
                Return 0
            End If

        Catch ex As Exception
            Throw New Exception("Exceção no método 'VisitasPorDia'." & Environment.NewLine & Environment.NewLine & ex.Message)
        End Try

    End Function
    Public Function VerificarVisitaExiste(ByVal IDCliente As Int32, ByVal IDContato As Int32, ByVal dtData As Date) As Boolean
        Dim ssql As String
        Dim dsResultado As New DataSet
        Try
            ssql = "SELECT IDCliente" & vbCrLf
            ssql &= " FROM DC_CadVisitas" & vbCrLf
            ssql &= "WHERE IdCliente = " & IDCliente
            ssql &= "  AND IdContato = " & IDContato
            ssql &= "  AND DataVisita = '" & dtData.Date & "'" & vbCrLf
            ssql &= "  AND (IDHistorico IS NULL OR IDHistorico = 0)"

            dsResultado = MyBase.executarConsulta(ssql, "DC_CadVisitas")
            Return (Not dsResultado Is Nothing AndAlso dsResultado.Tables(0).Rows.Count > 0)
        Catch ex As Exception
            Throw New Exception("Exceção no método 'VerificarVisitaExiste'." & Environment.NewLine & Environment.NewLine & ex.Message)
        End Try

    End Function
    Public Function VisitasPorPeriodo(ByVal dtDataInicio As Date, ByVal dtDataFim As Date, _
                                     ByVal iIdCliente As Int32, ByVal iIdRegiao As Int32, _
                                     ByVal iIdContato As Int32, ByVal iIdUsuario As Int32, _
                                     ByVal bIncluirRealizadas As Boolean) As DataSet
        Dim sSql As String

        Try
            sSql = "     SELECT DC_Clientes.*, " & vbCrLf
            sSql &= "           DC_Regioes.IdRegiao, DC_Regioes.Regiao, " & vbCrLf
            sSql &= "           DC_CadVisitas.IdContato,DC_Contatos.nomeContato, " & vbCrLf
            sSql &= "           DC_CadVisitas.DataVisita, DC_CadVisitas.HoraVisita, " & vbCrLf
            sSql &= "           DC_Bairros.Bairro, DC_Estados.Estado, DC_Cidades.Cidade, " & vbCrLf
            sSql &= "           DC_CadVisitas.Observacoes as ObservacoesVisitas, " & vbCrLf
            sSql &= "           CASE ISNULL(DC_CadVisitas.IDHistorico,'0') WHEN '0' then 0 else 1 end as Realizada" & vbCrLf
            sSql &= "      FROM DC_CadVisitas " & vbCrLf
            sSql &= " INNER JOIN DC_Clientes" & vbCrLf
            sSql &= "        ON DC_Clientes.IDCliente = DC_CadVisitas.IDCliente" & vbCrLf
            sSql &= " LEFT JOIN DC_Bairros" & vbCrLf
            sSql &= "        ON DC_Bairros.IDBairro = DC_Clientes.IDBairro" & vbCrLf
            sSql &= " LEFT JOIN DC_Cidades" & vbCrLf
            sSql &= "        ON DC_Cidades.IDCidade = DC_Clientes.IDCidade" & vbCrLf
            sSql &= " LEFT JOIN DC_Regioes" & vbCrLf
            sSql &= "        ON DC_Regioes.IDRegiao = DC_Clientes.IDRegiao" & vbCrLf
            sSql &= " LEFT JOIN DC_Estados" & vbCrLf
            sSql &= "        ON DC_Estados.IDEstado = DC_Clientes.IDEstado" & vbCrLf
            sSql &= " LEFT JOIN DC_Contatos" & vbCrLf
            sSql &= "        ON DC_Contatos.IdContato = DC_CadVisitas.IdContato" & vbCrLf
            sSql &= "     WHERE 1 = 1" & vbCrLf

            If (dtDataInicio <> Date.MinValue And dtDataFim <> Date.MinValue) Then
                sSql &= "       AND DC_CadVisitas.DataVisita BETWEEN '" & dtDataInicio & "'  AND '" & dtDataFim & "'" & vbCrLf
            End If

            If (iIdCliente > 0) Then
                sSql &= "       AND DC_CadVisitas.IDCliente = @IDCliente"
            End If

            If (iIdRegiao > 0) Then
                sSql &= "       AND DC_Regioes.IDRegiao = @IDRegiao"
            End If

            If (iIdContato > 0) Then
                sSql &= "       AND DC_Contatos.IDContato = @IDContato"
            End If

            If (iIdUsuario > 0) Then
                sSql &= "       AND DC_CadVisitas.IdUsuario = @IDUsuario"
            End If

            If (Not bIncluirRealizadas) Then
                sSql &= "       AND ISNULL(DC_CadVisitas.IdHistorico,0)=0 "
            End If

            With MyBase.SQLCmd.Parameters
                .Clear()

                If (iIdCliente > 0) Then
                    .AddWithValue("@IDCliente", iIdCliente)
                End If

                If (iIdContato > 0) Then
                    .AddWithValue("@IDContato", iIdContato)
                End If

                If (iIdRegiao > 0) Then
                    .AddWithValue("@IDRegiao", iIdRegiao)
                End If

                If (iIdUsuario > 0) Then
                    .AddWithValue("@IDUsuario", iIdUsuario)
                End If

            End With

            Return MyBase.executarConsulta(sSql, "DC_CadVisitas")

        Catch ex As Exception
            Throw New Exception("Exceção no método 'VisitasAgendadas'." & Environment.NewLine & Environment.NewLine & ex.Message)
        End Try

    End Function

#End Region

End Class
